package com.lianxi.exacl;

import com.lianxi.model.CountInfo;
import com.lianxi.model.CountItem;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.junit.Test;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

import static org.apache.poi.hssf.record.ExtendedFormatRecord.CENTER;
import static org.apache.poi.hssf.record.ExtendedFormatRecord.THIN;


public class TestExacl {


    @Test
    public void test() throws IOException {
        //创建HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook();
//创建HSSFSheet对象
        HSSFSheet sheet = wb.createSheet("sheet0");
//创建HSSFRow对象
        HSSFRow row = sheet.createRow(0);
//创建HSSFCell对象
        HSSFCell cell = row.createCell(0);
        HSSFCell cell1 = row.createCell(2);
        cell1.setCellValue("第一行第二列的值：5");
//设置单元格的值
        cell.setCellValue("单元格中的中文");
//输出Excel文件
        FileOutputStream output = new FileOutputStream("e:\\desktop\\workbook.xls");
        wb.write(output);
        output.flush();
    }


    public static InputStream test2() throws IOException {
        //文件名
        String filename = "";

        //创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        //建立新的sheet对象（excel的表单）
        HSSFSheet sheet = wb.createSheet("成绩表");
        //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        HSSFRow row1 = sheet.createRow(0);
        //创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
        HSSFCell cell = row1.createCell(0);
        //设置单元格内容
        cell.setCellValue("学员考试成绩一览表");
        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        //在sheet里创建第二行
        HSSFRow row2 = sheet.createRow(1);
        //创建单元格并设置单元格内容
        row2.createCell(0).setCellValue("姓名");
        row2.createCell(1).setCellValue("班级");
        row2.createCell(2).setCellValue("笔试成绩");
        row2.createCell(3).setCellValue("机试成绩");
        //在sheet里创建第三行
        HSSFRow row3 = sheet.createRow(2);
        row3.createCell(0).setCellValue("李明");
        row3.createCell(1).setCellValue("As178");
        row3.createCell(2).setCellValue(87);
        row3.createCell(3).setCellValue(78);

        //输出Excel文件
        //将workbook转换为流的形式
        InputStream input = null;
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        wb.write(os);
        input = new ByteArrayInputStream(os.toByteArray());

        //将文件输出本地
        FileOutputStream output = new FileOutputStream("e:\\desktop\\workbook.xls");
        wb.write(output);
        output.flush();
        return input;
    }

    public static InputStream test3() throws IOException {
        //表头
        List<String> datelist = new ArrayList<>();
        datelist.add("2022-1");
        datelist.add("2022-2");
        datelist.add("2022-3");
        datelist.add("2022-4");
        datelist.add("2022-5");
        //假数据
        List<CountItem> countList = new ArrayList<>();
        countList.add(new CountItem("2022-1", "20"));
        countList.add(new CountItem("2022-2", "30"));
        countList.add(new CountItem("2022-3", "40"));
        countList.add(new CountItem("2022-4", "50"));
        countList.add(new CountItem("2022-5", "50"));
        //假数据
        List<CountItem> countList2 = new ArrayList<>();
        countList2.add(new CountItem("2022-1", "50"));
        countList2.add(new CountItem("2022-2", "60"));
        countList2.add(new CountItem("2022-3", "40"));
        countList2.add(new CountItem("2022-4", "30"));
        countList2.add(new CountItem("2022-5", "50"));


        List<CountInfo> countInfoList = new ArrayList<>();
        CountInfo c1 = new CountInfo();
        c1.setSitename("济源市");
        c1.setCountItemList(countList);
        CountInfo c2 = new CountInfo();
        c2.setSitename("安阳市");
        c2.setCountItemList(countList2);

        countInfoList.add(c1);
        countInfoList.add(c2);

        //开始创建一个excel
        HSSFWorkbook workbook = new HSSFWorkbook();

        List<String> sheetList = new ArrayList<>();
        sheetList.add("新闻信息导入量");
        sheetList.add("ceshi 导入量");
        sheetList.add("ceshi2息导入量");
        for (String s : sheetList) {
            HSSFSheet sheet = workbook.createSheet(s);
            // 列宽
            for (int i = 0; i <= datelist.size(); i++) {
                sheet.setColumnWidth(i, 30 * 255);
            }

            // 字体样式
            Font fontStyle = workbook.createFont();
            fontStyle.setBold(false); // 加粗
            fontStyle.setFontName("黑体"); // 字体
            fontStyle.setFontHeightInPoints((short) 18); // 大小

            // 单元格样式
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);

            // 边框，居中
            cellStyle.setAlignment(CENTER);
            cellStyle.setBorderBottom(THIN);
            cellStyle.setBorderLeft(THIN);
            cellStyle.setBorderRight(THIN);
            cellStyle.setBorderTop(THIN);

            // 创建第一行并填充值
            HSSFRow row1 = sheet.createRow(0);
            HSSFCell cellB1 = row1.createCell(0);
            cellB1.setCellValue(s);
            cellB1.setCellStyle(cellStyle);

            // 合并首行单元格  // 起始行, 终止行, 起始列, 终止列
            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, datelist.size());
            sheet.addMergedRegion(cra);
            sheet.setDefaultRowHeight((short) 300);

            // 设置第一行高度
            sheet.getRow(0).setHeightInPoints(30);

            // 合并单元格后设置该行样式
            sheet.getRow(0).getCell(0).setCellStyle(cellStyle);

            // 创建第二行添加表头内容
            HSSFRow r2 = sheet.createRow(1);
            for (int i = 0; i < datelist.size(); i++) {
                HSSFCell cell = r2.createCell(i + 1);
                cell.setCellValue(datelist.get(i));
                cell.setCellStyle(cellStyle);
            }

            //从第三行开始填充数据，先填充列
            for (int i = 0; i < countInfoList.size(); i++) {
                HSSFRow row = sheet.createRow(2 + i);
                HSSFCell cell = row.createCell(0);
                cell.setCellValue(countInfoList.get(i).getSitename());
                cell.setCellStyle(cellStyle);
                for (int j = 0; j < datelist.size(); j++) {
                    HSSFCell cell1 = row.createCell(j + 1);
                    cell1.setCellValue(countInfoList.get(i).getCountItemList().get(j).getSum());
                    cell1.setCellStyle(cellStyle);
                }
            }

        }


        // 输出到本地
        String excelName = "e:\\desktop\\workbook.xls";
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(excelName);
            workbook.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (out != null)
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            out = null;
        }

        return null;
    }


    public static void main(String[] args) throws IOException {
//        InputStream inputStream = test2();
        InputStream inputStream = test3();
        List<String> sheetList = new ArrayList<>();
        sheetList.add("新闻信息导入量");
        sheetList.add("依法信息导入量");
        sheetList.add("领导信箱导入量");
        //表头
        List<String> datelist = new ArrayList<>();
        datelist.add("2022-1");
        datelist.add("2022-2");
        datelist.add("2022-3");
        datelist.add("2022-4");
        datelist.add("2022-5");
        //假数据
        List<CountItem> countList = new ArrayList<>();
        countList.add(new CountItem("2022-1", "20"));
        countList.add(new CountItem("2022-2", "30"));
        countList.add(new CountItem("2022-3", "40"));
        countList.add(new CountItem("2022-4", "50"));
        countList.add(new CountItem("2022-5", "50"));
        //假数据
        List<CountItem> countList2 = new ArrayList<>();
        countList2.add(new CountItem("2022-1", "50"));
        countList2.add(new CountItem("2022-2", "60"));
        countList2.add(new CountItem("2022-3", "40"));
        countList2.add(new CountItem("2022-4", "30"));
        countList2.add(new CountItem("2022-5", "50"));
        List<CountInfo> countInfoList = new ArrayList<>();
        CountInfo c1 = new CountInfo();
        c1.setSitename("济源市");
        c1.setCountItemList(countList);
        CountInfo c2 = new CountInfo();
        c2.setSitename("安阳市");
        c2.setCountItemList(countList2);
        countInfoList.add(c1);
        countInfoList.add(c2);
        HSSFWorkbook workbook = new HSSFWorkbook();
        for (String s : sheetList) {
            HSSFSheet sheet = workbook.createSheet(s);
            workbook = test4(workbook, sheet, countInfoList, datelist);
        }
        // 输出到本地
        String excelName = "e:\\desktop\\workbook.xls";
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(excelName);
            workbook.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (out != null)
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            out = null;
        }



    }


    public static HSSFWorkbook test4(HSSFWorkbook workbook, HSSFSheet sheet, List<CountInfo> countInfoList, List<String> datelist) throws IOException {

        // 字体样式
        Font fontStyle = workbook.createFont();
        fontStyle.setBold(false); // 加粗
        fontStyle.setFontName("黑体"); // 字体
        fontStyle.setFontHeightInPoints((short) 18); // 大小

        // 单元格样式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);

        // 边框，居中
        cellStyle.setAlignment(CENTER);
        cellStyle.setBorderBottom(THIN);
        cellStyle.setBorderLeft(THIN);
        cellStyle.setBorderRight(THIN);
        cellStyle.setBorderTop(THIN);


        // 列宽
        for (int i = 0; i <= datelist.size(); i++) {
            sheet.setColumnWidth(i, 30 * 255);
        }

        // 创建第一行并填充值
        HSSFRow row1 = sheet.createRow(0);
        HSSFCell cellB1 = row1.createCell(0);
        cellB1.setCellValue(sheet.getSheetName());
        cellB1.setCellStyle(cellStyle);

        // 合并首行单元格  // 起始行, 终止行, 起始列, 终止列
        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, datelist.size());
        sheet.addMergedRegion(cra);
        sheet.setDefaultRowHeight((short) 300);

        // 设置第一行高度
        sheet.getRow(0).setHeightInPoints(30);

        // 合并单元格后设置该行样式
        sheet.getRow(0).getCell(0).setCellStyle(cellStyle);

        // 创建第二行添加表头内容
        HSSFRow r2 = sheet.createRow(1);
        for (int i = 0; i < datelist.size(); i++) {
            HSSFCell cell = r2.createCell(i + 1);
            cell.setCellValue(datelist.get(i));
            cell.setCellStyle(cellStyle);
        }

        //从第三行开始填充数据，先填充列
        for (int i = 0; i < countInfoList.size(); i++) {
            HSSFRow row = sheet.createRow(2 + i);
            HSSFCell cell = row.createCell(0);
            cell.setCellValue(countInfoList.get(i).getSitename());
            cell.setCellStyle(cellStyle);
            for (int j = 0; j < datelist.size(); j++) {
                HSSFCell cell1 = row.createCell(j + 1);
                cell1.setCellValue(countInfoList.get(i).getCountItemList().get(j).getSum());
                cell1.setCellStyle(cellStyle);
            }
        }

        return workbook;
    }


//        // 输出到本地
//        String excelName = "e:\\desktop\\workbook.xls";
//        FileOutputStream out = null;
//        try {
//            out = new FileOutputStream(excelName);
//            workbook.write(out);
//            out.flush();
//            out.close();
//        } catch (Exception e) {
//            e.printStackTrace();
//        } finally {
//            if (out != null)
//                try {
//                    out.close();
//                } catch (IOException e) {
//                    e.printStackTrace();
//                }
//            out = null;
//        }
//
//        return null;
//    }


}
